Microsoft Excel (2013)

In this example, we create an Excel report that counts the number of incoming and outgoing loans and displays them in a table sorted by object condition status.

In EMu:

1. Search for or retrieve a group of records on which to report.

   In this example we search for records in the Loans module.

2. Select Reports on the Home tab of the Ribbon to display the Reports box.
3. Click New in the Reports box to display the Report Properties box.
4. On the Report Type tab, enter a descriptive name for the Report.
5. Select Microsoft Excel from the Type drop list:

   

6. On the Fields tab, add the fields you wish to include in the report.

   In this example we add:

   • Loan Direction
   • Commencement Date: (Loan Commencement Details)
   • Condition Status : (Condition Check)

   

7. Make changes to the Sort Order, Sort Options and Security tabs as required.

   Note: For full details of how to create the report in EMu, see 1: Create the report in EMu.

8. Click OK to add the new report to the Reports box:

   

9. Run the report:
   1. Select the report in the Reports box.
   2. Click Report All.

   A message displays indicating that your report does not exist on the server. This is to be expected as the report has not yet been built in the third party application (e.g. Excel):

   

10. Click Yes.

    Excel will open.

    If you click No, MS Excel will not open, but the output files will still be generated and copied to your computer.

11. Open a Blank workbook.

Next we:

• Establish the connection between EMu and Excel (specifying the DSN of the EMu data source).
• Add fields to the Excel report and specify any joins between tables.
• Configure the report to refresh the data each time the report is run.

With Excel 2013 all of this can be managed without the need to write Visual Basic code:

1. On the Data tab (1), select From Other Sources (2)>From Microsoft Query (3):

   

   The Choose Data Source box displays.

2. Select the data source for the EMu module in which you are running the report. In this example we select EMu Loans:

   

   This step establishes the connection between EMu and Excel by specifying the DSN of the EMu data source.

   Note: Be sure that the Use the Query Wizard to create/edit queries checkbox is ticked.

3. Click OK to continue.

   The Query Wizard - Choose Columns box displays with a list of the data files (tables) available in the data source specified at Step 2.

   Expanding each table reveals which fields are available:

   

   Next we add fields to the report and specify any joins between tables.

4. To include all available columns from both tables in our report:
   1. In the Available tables and columns pane select a table (e.g. eloans.csv).
   2. Click .

      All columns available for the table are added to the Columns in your query pane.

   3. Repeat Steps 1-2 for any other tables (or select a column and click ).

      Be sure to include the common key column for each table (e.g. eloans_key in this example). This is essential for specifying the join between tables.

      In this example, we've included all but one column:

      

5. Click Next> to continue.

   A warning message displays indicating that it is necessary to join the tables manually by "dragging the fields to join between the tables".

6. Click OK.

   The Microsoft Query window displays. Here we see the tables included in the query, and the columns in each table, as well as the data in each column:

   

7. To join the two tables in this example:
   1. Click the common key column in the left table (eloans_key in this example).
   2. Drag and drop it onto the matching key column in the other table.

   The two tables are joined (1) and the records in each table are merged (2):

   

   In the Microsoft Query window it is possible to sort data, add criteria (filters), and specify SQL statements, etc.:

   

8. Click Return Data to display the Import Data box:

   

9. By default, data will be placed on the spreadsheet in the top left cell (A1). To place it elsewhere, click a cell on the worksheet.
10. Click Properties to display the Connection Properties box.
11. Select the Refresh data when opening the file checkbox (placing a tick in the box):

    

    This step ensures that when the report is run, the data is refreshed.

12. Click OK to close the Connection Properties box.
13. Click OK to close the Import Data box.

    Report data is loaded into the Worksheet.

    EMu and Excel are now set up to communicate with each other and you are now in a position to format your report as required. This can involve VB code. See Example of an Excel report for a demonstration.

14. Save and close the spreadsheet as type: Excel Macro-Enabled Workbook (e.g. Loans.xlsm).

Finally we save the report back to EMu.

Note: The main report document must be closed before you can link it to the EMu report.

1. Open the module for which the report was created.
2. Search for or retrieve a group of records on which to report.
3. From the Ribbon, select Reports on the Home tab of the Ribbon to display the Reports box.
4. Select the new report and click Properties to display the Report Properties box.
5. On the Report Type tab, click Upload.

   Browse to where you saved the report and double-click the report file.

   The report will now appear in the Report Properties box:

   

6. Click OK to close the Report Properties box and return to the Reports box.
7. Run the report.

   MS Excel opens with the generated report.

After the EMu data has been exported into an Excel report it is possible to format and extend the report so that it presents the information in the best way possible. In some cases, further Visual Basic code is required.

The following is an example of a report that totals the number of incoming and outgoing loans and displays a breakdown according to object type:

The Visual Basic code that produces this report is:

Private Sub Workbook_Open()

    ActiveWorkbook.RefreshAll

    Dim dataSheet As Worksheet

    Dim emptyRow As Integer

    Dim lastDataRow As Integer

    Dim incomingCount As Integer

    Dim outgoingCount As Integer

    'Be sure to rename the worksheet to Data

    Set dataSheet = ThisWorkbook.Worksheets("Data")

    'Find the first empty row

    emptyRow = dataSheet.Range("A" & Rows.Count).End(xlUp).Row

    lastDataRow = emptyRow

    emptyRow = lastDataRow + 3

    'Add and format summary title

    Worksheets("Data").Range("A" + (CStr(emptyRow))).Value = "Loan Summary"

    Worksheets("Data").Range("A" + (CStr(emptyRow)) + ":B" + (CStr(emptyRow))).Font.Bold = True

    Worksheets("Data").Range("A" + (CStr(emptyRow)) + ":B" + (CStr(emptyRow))).Interior.Color = RGB(91, 155, 213)

    Worksheets("Data").Range("A" + (CStr(emptyRow)) + ":B" + (CStr(emptyRow))).Font.Color = RGB(255, 255, 255)

    'Incoming

    emptyRow = emptyRow + 1

    incomingCount = Application.WorksheetFunction.CountIf(Range("B2:B" + (CStr(lastDataRow))), "Incoming")

    Worksheets("Data").Range("A" + (CStr(emptyRow))).Value = "Incoming"

    Worksheets("Data").Range("B" + (CStr(emptyRow))).Value = incomingCount

    'Outgoing

    emptyRow = emptyRow + 1

    outgoingCount = Application.WorksheetFunction.CountIf(Range("B2:B" + (CStr(lastDataRow))), "Outgoing")

    Worksheets("Data").Range("A" + (CStr(emptyRow))).Value = "Outgoing"

    Worksheets("Data").Range("B" + (CStr(emptyRow))).Value = outgoingCount

    'Total

    emptyRow = emptyRow + 1

    Worksheets("Data").Range("A" + (CStr(emptyRow))).Value = "Total"

    Worksheets("Data").Range("A" + (CStr(emptyRow))).Font.Bold = True

    Worksheets("Data").Range("B" + (CStr(emptyRow))).Value = incomingCount + outgoingCount

    Worksheets("Data").Range("B" + (CStr(emptyRow))).Font.Bold = True

    'Add and format summary title

    emptyRow = dataSheet.Range("D" & Rows.Count).End(xlUp).Row

    lastDataRow = emptyRow

    emptyRow = lastDataRow + 3

    Worksheets("Data").Range("D" + (CStr(emptyRow))).Value = "Condition Status Summary"

    Worksheets("Data").Range("D" + (CStr(emptyRow)) + ":E" + (CStr(emptyRow))).Font.Bold = True

    Worksheets("Data").Range("D" + (CStr(emptyRow)) + ":E" + (CStr(emptyRow))).Interior.Color = RGB(91, 155, 213)

    Worksheets("Data").Range("D" + (CStr(emptyRow)) + ":E" + (CStr(emptyRow))).Font.Color = RGB(255, 255, 255)

    emptyRow = emptyRow + 1

    Dim dict As Object

    Set dict = CreateObject("scripting.dictionary")

    Dim varray As Variant, element As Variant

    varray = Range("E2:E" + (CStr(lastDataRow))).Value

    If (lastDataRow > 2) Then

        'Generate unique list and count

        For Each element In varray

            If dict.exists(element) Then

                dict.Item(element) = dict.Item(element) + 1

            Else

                dict.Add element, 1

            End If

        Next

        'Paste report somewhere

        Worksheets("Data").Range("D" + (CStr(emptyRow))).Resize(dict.Count, 1).Value = _

            WorksheetFunction.Transpose(dict.keys)

        Worksheets("Data").Range("E" + (CStr(emptyRow))).Resize(dict.Count, 1).Value = _

            WorksheetFunction.Transpose(dict.items)

    Else

        Worksheets("Data").Range("D" + (CStr(emptyRow))).Value = Worksheets("Data").Range("E" + (CStr(lastDataRow))).Value

        Worksheets("Data").Range("E" + (CStr(emptyRow))).Value = 1

    End If

End Sub

This first example is a simple report on single value fields from the Catalogue module. The VBA code provided in this example will automatically populate headings and row data for each column selected.

In EMu:

1. Search for or retrieve a group of records on which to report.

   In this example we search for records in the Loans module.

2. Click Reports on the Home tab of the Ribbon to display the Reports box.
3. Click New in the Reports box to display the Report Properties box.
4. On the Report Type tab, enter a descriptive name for the Report.
5. Select Microsoft ADO Report from the Type drop list:

   

6. On the Fields tab, add the fields you wish to include in the report.
7. Make changes to the Sort Order, Sort Options and Security tabs as required.

   Note: For full details of how to create the report in EMu, see 1: Create the report in EMu.

8. Click OK to add the new report to the Reports box.
9. Run the report:
   1. Select the report in the Reports box.
   2. Click Report All.

   A message displays indicating that your report does not exist on the server. This is to be expected as the report has not yet been built in the third party application (e.g. Excel):

   

10. Click Yes.

    An xml file is generated and saved with the data from your record set. The location of this file can vary, but typically it can be found in:

    C:\Users\[your username]\AppData\Local\KESoftware\Reports\e[module name]

    For example, a report run in the Parties module, will save the xmldata file to:

    C:\Users\[your username]\AppData\Local\KESoftware\Reports\eparties

    Note: If you click No, the output files will still be generated and copied to your computer.

    What happens next depends on the Reports|Extension Registry entry. For example, if a Reports|Extension Registry entry associates the Microsoft ADO report type with Excel files, Microsoft Excel will open.

    Tip: If you want Microsoft ADO reports to be associated with MS Word, edit the Reports|Extension Registry entry accordingly. Otherwise, simply close Excel, open MS Word instead and add your code to Word. All that matters is that you create a connection between the Microsoft report (whether it is Word, Excel, Access, etc.) and the xmldata file.

11. In this example we open a Blank Excel workbook.

1. On the Developer tab in Excel, click Visual Basic.

   Microsoft Visual Basic for Applications will open.

   Tip: If the Developer tab does not display, ensure that Excel has been set up correctly.

2. Double-click Sheet1 in the VBAProject pane.
3. Add your code.
   In this example we copy and paste the following VB code

   Sub OpenAdoFile()

   	Dim RecordSet As ADODB.RecordSet

   	Dim Worksheet As Excel.Worksheet

   	Dim h As Long

   	Dim col As Long

   	Dim datarow As Long

   	Dim source As String

   	'Get the persisted record set. This example specifies ecatalogue

   	' Be sure to specify the correct table in your code

   	source = Environ("LocalAppData") & "\KESoftware\Reports\ecatalogue\xmldata.xml"

   	Set RecordSet = New ADODB.RecordSet

   	RecordSet.Open source, "Provider=MSPersist"

   	'Get the active page to send the data to

   	Set Worksheet = ThisWorkbook.ActiveSheet

   	Application.Visible = True

   	'Put out all of the column headers

   	col = 1

   	ListColumnNames Worksheet, RecordSet, col

   	'Print out all the row data

   	While Not RecordSet.EOF

   		col = 1

   		datarow = datarow + 1

   		For h = 0 To RecordSet.Fields.count - 1

   			Worksheet.Cells(datarow + 1, col).Value = RecordSet.Fields(h).Value

   			col = col + 1

   		Next

   		RecordSet.MoveNext

   	Wend

   	Worksheet.Range("A1").CurrentRegion.Select

   	Worksheet.Columns.AutoFit

   	Set RecordSet = Nothing

   End Sub

   Private Sub ListColumnNames(ByVal ws As Excel.Worksheet, ByVal rs As ADODB.RecordSet, ByRef col As Long)	

   	Dim i As Long

   	'Loop through the record set pulling out the column names

   	For i = 0 To rs.Fields.count - 1

   		ws.Cells(1, col).Value = rs.Fields(i).Name

   		col = col + 1

   	Next

   End Sub

   

4. Double-click ThisWorbook in the VBAProject pane and add the following code:

   Sub Workbook_Open()

   'Load up the ADO File

   Sheet1.OpenAdoFile

   End Sub

   

5. Save the report as macro-enabled and upload it to your EMu report.
6. Run the report: